Course: Advanced Excel
Topic: Automating Reports Using Power Query and Macros
Course Link: Advanced Excel Course
Demo Link: Register for a Course Demo
Website: JustAcademy Official Website
Report automation is the process of reducing repetitive manual work by using Excel tools to import, clean, transform, calculate, format, and present data. Instead of preparing the same report from scratch every day, week, or month, an automated workbook can refresh data and repeat predefined steps.
In Advanced Excel, two useful tools for report automation are Power Query and Macros. Power Query handles data importing and transformation, while macros automate actions such as formatting worksheets, refreshing reports, preparing summaries, and exporting files.
For example, a sales analyst may receive a new sales transaction file every month. Rather than manually cleaning the data, calculating totals, and preparing summaries, the analyst can build a repeatable process that updates the report whenever new data becomes available.
Power Query is an Excel data preparation and transformation tool. It can connect to sources such as Excel workbooks, CSV files, text files, folders, and supported databases. It allows users to clean and reshape data through repeatable steps without manually repeating the same operations each time.
Suppose a company stores sales transactions in monthly CSV files. Each file contains Order ID, Date, Product, Region, Quantity, and Sales Amount. Power Query can combine the files, remove blank rows, assign correct data types, and load the cleaned records into Excel.
A macro is a recorded or programmed sequence of actions that Excel can execute to automate repetitive tasks. Macros are commonly created using the Visual Basic for Applications (VBA) programming language.
For example, a macro can refresh queries, apply consistent number formatting, create a timestamp, activate a summary sheet, or prepare a workbook for distribution.
| Feature | Power Query | Macros |
|---|---|---|
| Primary purpose | Importing and transforming data | Automating workbook actions |
| Typical tasks | Cleaning, filtering, merging, appending | Formatting, refreshing, copying, exporting |
| Technology | Power Query interface and M language | VBA for traditional Excel macros |
| Repeatability | Reapplies saved transformation steps during refresh | Repeats recorded or programmed actions |
| Best suited for | Preparing reliable, structured datasets | Automating actions across worksheets and reports |
| Important limitation | Source changes can require query adjustments | Macros may require security approval and compatible desktop Excel |
Power Query and macros serve different purposes. They can be used together to create an efficient reporting process, but neither tool automatically guarantees that a report is accurate. Source data, transformation logic, formulas, and output should still be validated.
Before building an automated report, define the source data, transformation rules, output format, refresh process, and validation checks.
Power Query can import data from several supported sources. The exact menu names can vary slightly by Excel version.
After the query has been configured, refreshing it reapplies the saved steps to the available source data. If the source location, file structure, or column names change, the query may need to be updated.
Clean data is essential for dependable reports. Power Query can perform many common cleaning operations as saved transformation steps.
| Operation | Purpose | Example |
|---|---|---|
| Remove Columns | Exclude unnecessary fields | Remove internal notes from a sales report |
| Remove Rows | Exclude blank or irrelevant records | Remove completely empty rows |
| Change Data Type | Interpret values correctly | Convert Order Date to Date |
| Trim and Clean | Remove unwanted spaces and certain nonprinting characters | Standardize customer names |
| Remove Duplicates | Remove duplicate values according to selected columns | Deduplicate customer records using an appropriate key |
| Replace Values | Standardize inconsistent entries | Change "N. Region" to "North" |
| Merge Queries | Join related tables using matching fields | Combine sales records with a product lookup table |
| Append Queries | Stack rows from similar datasets | Combine January, February, and March sales files |
Suppose a source contains the following records:
| Order ID | Region | Sales Amount |
|---|---|---|
| 1001 | North | 1500 |
| 1002 | north | 2300 |
| 1003 | South | 1800 |
In Power Query, trim unnecessary spaces and standardize the Region values. This helps prevent inconsistent labels from appearing as separate categories in PivotTables and charts. If case differences matter to a particular process, define the required standard explicitly.
Many organizations receive recurring files with the same basic structure. Power Query's folder connector can combine supported files from a folder into one dataset, which is useful for monthly sales reports, attendance reports, expense summaries, and inventory records.
For future reporting periods, place the new files in the expected location and refresh the query. The result depends on the query's file filters, folder path, source structure, and transformation settings. Avoid mixing unrelated workbooks or backup copies into the source folder.
Once Power Query has prepared the dataset, Excel can summarize the results using PivotTables, formulas, and charts.
For example, a sales report may include:
Use an Excel Table or another suitable structured output where practical. PivotTables and formulas should reference the correct data range, and PivotTables may need to be refreshed after their underlying data changes.
=SUM(SalesData[Sales Amount])
=COUNT(SalesData[Order ID])
=AVERAGE(SalesData[Sales Amount])These examples assume that the loaded Excel table is named SalesData and contains the specified columns. Adjust the names to match your workbook.
Excel's macro recorder can capture many routine actions and generate VBA code. It is useful for learning and automating simple formatting tasks, although recorded macros may need editing to handle changing data ranges reliably.
FormatSalesReport. Do not use spaces in the macro name.To retain VBA macros in the workbook, save it in a macro-enabled format such as .xlsm. A standard .xlsx workbook does not retain VBA macro code.
The following example refreshes workbook connections and applies basic formatting to a worksheet named Sales Report. The sheet is expected to contain a report title in row 1 and column headings in row 3.
Sub RefreshAndFormatSalesReport()
Dim ws As Worksheet
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
ThisWorkbook.RefreshAll
Set ws = ThisWorkbook.Worksheets("Sales Report")
With ws
.Rows(3).Font.Bold = True
.Rows(3).WrapText = True
.Columns.AutoFit
.Range("A1").Font.Bold = True
.Range("A1").Font.Size = 16
.Range("A1").Value = "Sales Report"
.Range("A2").Value = "Last macro run: " & _
Format(Now, "dd-mmm-yyyy hh:mm")
End With
CleanExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "The report could not be completed: " & _
Err.Description, vbExclamation
Resume CleanExit
End SubSub RefreshAndFormatSalesReport() defines the macro.ThisWorkbook.RefreshAll requests a refresh of workbook data connections and related refreshable objects.Worksheets("Sales Report") refers to the worksheet by name.Font.Bold makes the specified text bold.Columns.AutoFit adjusts column widths to fit their contents.Format(Now, ...) creates a timestamp based on the current system date and time.On Error GoTo ErrorHandler directs execution to a basic error-handling section if a VBA error occurs.Application.ScreenUpdating = False reduces screen redraws during the macro. The cleanup section turns it back on.Important: RefreshAll may start background refresh operations that continue after the VBA statement returns. For dependable automation, configure query and connection refresh settings appropriately and explicitly wait for completion where the connection type supports it. Do not assume that the timestamp in this example proves that every query has finished refreshing. Test the macro with the actual workbook and its data connections.
Power Query and VBA can work together to reduce manual reporting tasks. Power Query prepares the data, while a macro coordinates supported workbook actions after the data is ready.
For simple workflows, a macro can call ThisWorkbook.RefreshAll and then run formatting steps. For more complex workbooks, refresh completion and dependency order should be managed explicitly. Some connections refresh asynchronously, and different connection types support different completion methods.
A retail company receives a new CSV file each month with sales transactions. The reporting team needs a monthly summary by region and product.
| Order ID | Date | Product | Region | Quantity | Sales Amount |
|---|---|---|---|---|---|
| 2001 | 01-Oct-2026 | Laptop | West | 2 | 90000 |
| 2002 | 03-Oct-2026 | Monitor | North | 3 | 36000 |
| 2003 | 05-Oct-2026 | Laptop | West | 1 | 45000 |
| 2004 | 07-Oct-2026 | Keyboard | South | 5 | 7500 |
SalesData.Refreshing Power Query does not always mean every report component has been updated in the required order. PivotTables, formulas, charts, and external connections should be checked separately.
A simple VBA procedure for refreshing PivotTables in a workbook is shown below:
Sub RefreshAllPivotTables()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
MsgBox "PivotTable refresh requests completed."
End SubThis macro requests a refresh for each PivotTable it can access. If the PivotTables depend on query results that have not finished refreshing, they may still display old data. Run the refresh process in the correct sequence and verify the displayed totals before distributing the report.
Automated reports may contain data connections, formulas, macros, and business information. Choose the appropriate file format and sharing method based on the report's purpose.
| Format | Use | Important Note |
|---|---|---|
.xlsx |
Sharing reports without VBA macro code | VBA macros are not retained in this format |
.xlsm |
Keeping a workbook with VBA macros | Recipients must use a compatible Excel environment and follow macro security policies |
.csv |
Sharing flat, tabular data | Does not retain workbook formatting, formulas, PivotTables, or macros |
.pdf |
Distributing a fixed-layout report | Usually intended for viewing rather than interactive analysis |
Before sharing, confirm that sensitive information is removed or appropriately protected, data sources are accessible to intended users, and the workbook does not contain unnecessary hidden sheets or confidential metadata.
| Problem | Possible Cause | Solution |
|---|---|---|
| Power Query cannot find a file | Source path changed or file was moved | Update the source path and verify folder permissions |
| New rows are missing | Source range, file filter, or query logic excludes them | Check the source selection, filters, and refresh status |
| Data types are incorrect | Dates or numbers are interpreted inconsistently | Set explicit data types and inspect locale settings |
| Duplicate records appear | Source files overlap or the query combines duplicates | Review source files and use a suitable deduplication key |
| Macro reports a worksheet error | Worksheet name is incorrect or the sheet is missing | Confirm the exact worksheet name |
| Macro does not run | Macros are disabled or the file is not macro-enabled | Check file format and follow approved security settings |
| PivotTable shows old values | PivotTable refreshed before the query finished | Ensure query refresh completion before refreshing the PivotTable |
| Report formatting changes unexpectedly | Macro uses fixed ranges or depends on selection | Use explicit worksheet and range references; test with varying row counts |
| Refresh takes too long | Large source files or complex transformations | Remove unnecessary columns early, filter appropriately, and optimize query steps |
| Report totals do not match | Missing rows, duplicate transactions, filters, or calculation differences | Compare row counts and totals against the original source |
Macros can execute code, so they should only be enabled when the workbook and its source are trusted. Do not bypass organizational security controls or enable unknown macros simply to make a report work.
Import a sales CSV file using Power Query. Remove blank rows, trim text values, set the correct data types, and load the cleaned data into an Excel Table.
Expected outcome: A consistent dataset ready for analysis.
Create a folder containing three sales CSV files with matching columns. Use Power Query to combine the files and create one consolidated table.
Expected outcome: A refreshable dataset that includes eligible records from all intended files.
Record a macro that formats a report heading, bolds column headers, adjusts column widths, and applies an appropriate date or currency format.
Expected outcome: A repeatable formatting procedure.
Create a workbook with Power Query, a sales summary PivotTable, a chart, and a macro that requests a data refresh and applies report formatting.
Expected outcome: A repeatable reporting workflow with a documented refresh sequence and validation checks.
Rename a worksheet used by a test macro, then run the macro. Observe the error message and update the code or worksheet name to handle the issue correctly.
Expected outcome: Better understanding of VBA error handling and workbook dependencies.
Project Objective: Build an Excel workbook that converts recurring sales files into a cleaned dataset and a management-ready dashboard.
Answer: Report automation uses repeatable processes to import data, clean it, calculate summaries, format results, and prepare reports with less manual effort.
Answer: Power Query is an Excel tool for connecting to data sources and applying repeatable steps to import, clean, combine, and transform data.
Answer: A macro is a recorded or programmed sequence of actions used to automate repetitive tasks. Traditional Excel VBA macros use Visual Basic for Applications.
Answer: Power Query is primarily used for data preparation and transformation. VBA macros automate workbook operations such as formatting, refreshing supported objects, and preparing outputs.
Answer: Yes. Power Query can combine supported files from a folder when their structures and transformation requirements are compatible.
Answer: Not necessarily. The refresh sequence and connection settings matter. PivotTables should be refreshed after the underlying data is available and updated.
Answer: Validation checks help identify missing rows, duplicates, incorrect data types, stale summaries, and mismatched totals before a report is distributed.
Answer: The Excel Macro-Enabled Workbook format, .xlsm, retains VBA macro code.
ThisWorkbook.RefreshAll do?Answer: It requests a refresh of refreshable data connections and related objects in the workbook. Depending on connection settings, some refresh operations may continue asynchronously.
Answer: Use stable source structures, explicit transformation rules, robust error handling, correct refresh sequencing, validation checks, backups, and clear documentation.
.xlsm when VBA macro code must be retained.Automating reports using Power Query and macros helps Excel users prepare recurring reports more consistently and efficiently. Power Query provides a repeatable way to import and transform source data, while macros can coordinate supported refresh, formatting, and reporting actions. When these tools are combined with PivotTables, formulas, charts, and validation checks, they can support a dependable reporting workflow.
The most important principle is to automate both the work and the quality checks. A useful automated report should be easy to refresh, clear to understand, safe to share, and accurate enough to support business decisions.
Explore the Advanced Excel Course to strengthen your skills in data transformation, formulas, dashboards, and reporting automation.
Interested in learning more? Register for a Course Demo or visit the JustAcademy website.
